package com.enfo.zjabank.dao;

import com.enfo.zjabank.model.UploadedFile;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhoum on 2016/7/22.
 */
@Repository
public class UploadDaoImpl extends BaseDao implements UploadDao {

    @Override
    public int upload(final UploadedFile ufile) {
        final String sql = "INSERT INTO buz_attachements(busi_code,busi_id,origin_name,attachment_suffix,filesize,input_user,input_dept,path,thumb_path,save_name,file_type) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        final String busiCode = ufile.getBusiCode();
        final KeyHolder key = new GeneratedKeyHolder();
        this.jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1,busiCode);
                ps.setString(2,ufile.getBusiId());
                ps.setString(3,ufile.getOriginName());
                ps.setString(4,ufile.getSuffix());
                ps.setInt(5, ufile.getSize());
                ps.setString(6, ufile.getInputUser());
                ps.setString(7,ufile.getInputDept());
                ps.setString(8,ufile.getPath());
                ps.setString(9,ufile.getThumbPath());
                ps.setString(10,ufile.getSaveName());
                ps.setString(11,ufile.getFileType());
                return ps;
            }
        },key);
        final int primaryKey = key.getKey().intValue();
        if(busiCode.startsWith("debt")){
            final String sqlToModify = "INSERT INTO buz_attachements_modify(busi_code,busi_id,origin_name,attachment_suffix,filesize,input_user,input_dept,path,thumb_path,save_name,file_type,attachmentid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
            this.jdbcTemplate.update(sqlToModify, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1,busiCode.replace("debt", "change"));
                    ps.setString(2,ufile.getBusiId());
                    ps.setString(3,ufile.getOriginName());
                    ps.setString(4, ufile.getSuffix());
                    ps.setInt(5, ufile.getSize());
                    ps.setString(6, ufile.getInputUser());
                    ps.setString(7,ufile.getInputDept());
                    ps.setString(8,ufile.getPath());
                    ps.setString(9,ufile.getThumbPath());
                    ps.setString(10,ufile.getSaveName());
                    ps.setString(11, ufile.getFileType());
                    ps.setInt(12, key.getKey().intValue());
                }
            });
        }
        return primaryKey;
    }

    @Override
    public int uploadModify(final UploadedFile ufile) {
        KeyHolder key = new GeneratedKeyHolder();
        final String sqlToModify = "INSERT INTO buz_attachements_modify(busi_code,busi_id,origin_name,attachment_suffix,filesize,input_user,input_dept,path,thumb_path,save_name,file_type) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        this.jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sqlToModify, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1,ufile.getBusiCode());
                ps.setString(2,ufile.getBusiId());
                ps.setString(3,ufile.getOriginName());
                ps.setString(4, ufile.getSuffix());
                ps.setInt(5, ufile.getSize());
                ps.setString(6, ufile.getInputUser());
                ps.setString(7,ufile.getInputDept());
                ps.setString(8,ufile.getPath());
                ps.setString(9,ufile.getThumbPath());
                ps.setString(10,ufile.getSaveName());
                ps.setString(11, ufile.getFileType());
                return ps;
            }
        },key);
        return key.getKey().intValue();
    }

    @Override
    public Map<String,Object> getFile(String busiCode,String id) {
        String sql = null;
        if(busiCode.startsWith("change")){
            sql = "select attachmentid,path,thumb_path,origin_name,file_type from buz_attachements_modify where modify_id=?";
        }else{
            sql = "select path,thumb_path,origin_name,file_type from buz_attachements where attachmentid=?";
        }
        Object[] params = new Object[]{id};
        return queryForMap(sql, params);
    }

    @Override
    public List<Map<String, Object>> listImgs(final String busiCode, String busiId) {
        String sql = null;
        String primaryKeyName = null;
        if(busiCode.startsWith("change")){
            primaryKeyName = "modify_id";
            sql = "select modify_id,origin_name,filesize,file_type from buz_attachements_modify where busi_code=? and busi_id=?";
        }else{
            primaryKeyName = "attachmentid";
            sql = "select attachmentid,origin_name,filesize,file_type from buz_attachements where busi_code=? and busi_id=?";
        }
        Object[] params = new Object[]{busiCode,busiId};
        final String finalPrimaryKeyName = primaryKeyName;
        List<Map<String, Object>> list = this.jdbcTemplate.query(sql, params, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("name", rs.getString("origin_name"));
                map.put("size", rs.getInt("filesize"));
                map.put("url", "/server/upload/get/file/" + busiCode + "/" + rs.getInt(finalPrimaryKeyName) + "/" + map.get("name"));
                if ("image".equals(rs.getString("file_type"))) {
                    map.put("thumbnailUrl", "/server/upload/get/thumbImg/" + busiCode + "/" + rs.getInt(finalPrimaryKeyName));
                }
                map.put("deleteUrl", "/server/upload/del/" + busiCode + "/" + rs.getInt(finalPrimaryKeyName));
                map.put("deleteType", "GET");
                return map;
            }
        });
        return list;
    }

    @Override
    public int del(String busiCode,String id) {
        if(busiCode.startsWith("change")){
            String delSql = "delete from buz_attachements_modify where modify_id=?";
            return this.jdbcTemplate.update(delSql, new Object[]{id});
        }else if(busiCode.startsWith("debt")){
            String sql1 = "delete from buz_attachements where attachmentid=?";
            String sql2 = "delete from buz_attachements_modify where attachmentid=?";
            if(this.jdbcTemplate.update(sql1, new Object[]{id})>0){
                return this.jdbcTemplate.update(sql2, new Object[]{id});
            }else{
                return 0;
            }
        }else{
            String sql = "delete from buz_attachements where attachmentid=?";
            return this.jdbcTemplate.update(sql, new Object[]{id});
        }
    }

    /**
     * 判断是否有上传权限
     * @param inputCode
     * @return
     */
    @Override
    public boolean uploadRole(String inputCode) {
        String sql = "select 1 from org_user_role where role_code='R01' and user_code=?";
        String result = queryForObject(sql, new Object[]{inputCode}, String.class);
        if(result!=null && "1".equals(result)){
            return true;
        }
        return false;
    }

    @Override
    public Map getDebtInfo(String debtId) {
        String sql = "select police_rState,justice_state,justice_exeState,sanction_mode,report_state from buz_escape_debts where debt_id=?";
        return queryForMap(sql,new Object[]{debtId});
    }

    @Override
    public Map getDebtChangeInfo(String debtId) {
        String sql = "select police_rState,justice_state,justice_exeState,sanction_mode from buz_escape_modify where modify_id=?";
        return queryForMap(sql,new Object[]{debtId});
    }

    public String getInputCode(String debtId){
        String sql = "select input_user from buz_escape_debts where debt_id=?";
        return queryForObject(sql,new Object[]{debtId},String.class);
    }

    @Override
    public String getDebtId(String modifyId) {
        String sql = "select debt_id from buz_escape_modify where modify_id=?";
        return this.jdbcTemplate.queryForObject(sql,new Object[]{modifyId},String.class);
    }

    @Override
    public void createSnapshot(String modifyId) {
        String sql = "insert into buz_attachements_snapshot(modify_id,attachmentid,busi_code,busi_id,save_name,origin_name,file_type,attachment_suffix,path,thumb_path,description,filesize,input_user,input_time,input_dept) select ?,attachmentid,busi_code,busi_id,save_name,origin_name,file_type,attachment_suffix,path,thumb_path,description,filesize,input_user,input_time,input_dept from buz_attachements where busi_code like 'debt%' and busi_id = (select debt_id from buz_escape_modify where modify_id=?)";
        this.jdbcTemplate.update(sql,new Object[]{modifyId,modifyId});
    }

    @Override
    public void mergeModify(String modifyId) {
        String delSql1 = "delete from buz_attachements where busi_id=(select debt_id from buz_escape_modify where busi_code like 'debt%' and modify_id=?)";
        String insertSql1 = "insert into buz_attachements (busi_code,busi_id,save_name,origin_name,file_type,attachment_suffix,path,thumb_path,description,filesize,input_user,input_time,input_dept)select REPLACE(busi_code,'change','debt'),busi_id,save_name,origin_name,file_type,attachment_suffix,path,thumb_path,description,filesize,input_user,input_time,input_dept from buz_attachements_modify where busi_id=(select debt_id from buz_escape_modify where modify_id=?)";
        String delSql2 = "delete from buz_attachements_modify where busi_id=(select debt_id from buz_escape_modify where modify_id=?)";
        String insertSql2 = "insert into buz_attachements_modify (attachmentid,busi_code,busi_id,save_name,origin_name,file_type,attachment_suffix,path,thumb_path,description,filesize,input_user,input_time,input_dept)select attachmentid,REPLACE(busi_code,'debt','change'),busi_id,save_name,origin_name,file_type,attachment_suffix,path,thumb_path,description,filesize,input_user,input_time,input_dept from buz_attachements where busi_code like 'debt%' and busi_id=(select debt_id from buz_escape_modify where modify_id=?)";
        this.jdbcTemplate.update(delSql1,new Object[]{modifyId});
        this.jdbcTemplate.update(insertSql1,new Object[]{modifyId});
        this.jdbcTemplate.update(delSql2,new Object[]{modifyId});
        this.jdbcTemplate.update(insertSql2,new Object[]{modifyId});
    }
}
